Análise de dados com MySQL
Olá, meu nome é Josiane Campos Moreira e gostaria de mostrar nesse portfólio, um pouco dos vários aprendizados que adquiri ao estudar a linguagem SQL, utilizando o sistema gerenciador de banco de dados MySQL. Para isso, escolhi um banco de dados onde irei fazer algumas análises.
O banco de dados escolhido é de uma loja de moda C2C online de sucesso com vários usuários registrados. A loja foi lançada pela primeira vez na Europa por volta de 2009 e depois expandida em todo o mundo.
No link abaixo, você conseguirá baixar o banco de dados já citado: https://www.kaggle.com/datasets/jmmvutu/ecommerce-users-of-a-french-c2c-fashion-store?select=countries_with_top_sellers_fashion_c2c.csv
Depois de baixar o banco de dados, foi obtido três tabelas em formato Excel, dessa forma, o próximo passo é importar os dados dessas tabelas para o MySQL e começar a análise de dados.
Tenho como objetivo fazer as seguintes análises:
1) Listar os usuários que estão localizados nos países com maiores vendas de moda.
2) Fazer uma lista da taxa de aprovação dos produtos vendidos, onde uma taxa de aprovação menor que 60% será considerada ruim.
3) Analisar qual dos gêneros tem maior participação nos produtos vendidos.
4) Analisar os usuários que ficaram muitos dias sem fazer login na E-commerce. Isso influenciará no número de produtos vendidos por eles?
Para começar a importação, vamos criar um banco de dados com o nome E_COMMERCE:
create database E_COMMERCE;
Com o banco de dados E_COMMERCE selecionado, irei criar as tabela para receber a importação dos dados:
create table users_dataset (identifierHash varchar (100), `type` varchar (20),country varchar (30),`language` varchar (30), socialNbFollowers varchar (50),socialNbFollows varchar (50), socialProductsLiked int,productsListed int, productsSold int, productsPassRate float, productsWished int, productsBought int, gender varchar (5),civilityGenderId varchar (5) ,civilityTitle varchar (20), hasAnyApp varchar (20),hasAndroidApp varchar (20),hasIosApp varchar (20), hasProfilePicture varchar (20),daysSinceLastLogin int,seniority float,
seniorityAsMonths float,seniorityAsYears float, countryCode varchar (20));
create table countries_with_top_seller_fashion (country varchar (30),
sellers int,topsellers int,topsellerratio float,femalesellersratio float, topfemalesellersratio float, femalesellers int,malesellers int ,topfemalesellers int, topmalesellers int, countrysoldratio float, bestsoldratio float, toptotalproductssold int, totalproductssold int, toptotalproductslisted int, totalproductslisted int,topmeanproductssold float, topmeanproductslisted float,meanproductssold float, meanproductslisted float, meanofflinedays float,topmeanofflinedays float, meanfollowers float, meanfollowing float, topmeanfollowers float,topmeanfollowing float);
create table comparison_of_sellers_by_gender_and_country (country varchar (30),sex varchar(20),nbsellers int,meanproductssold float,meanproductslisted float,meansellerpassrate float,totalproductssold int,totalproductslisted int, meanproductsbought float,meanproductswished float,meanproductsliked float, totalbought int, totalwished int,totalproductsliked int,meanfollowers float,meanfollows float, percentofappusers float,percentofiosusers float,meanseniority float);
Depois de executar os comandos acima, teremos três tabelas. Agora basta importar os dados contidos nas tabelas baixadas em formato Excel. Podemos fazer a importação dos dados utilizando a opção TABLE DATA IMPORT WIZARD e vamos visualizar todos os campos e os dados das tabelas criadas utilizando os seguintes códigos:
select * from comparison_of_sellers_by_gender_and_country;
select * from countries_with_top_seller_fashion;
select * from countries_with_top_seller_fashion;
select * from users_dataset;
Observação: Para a nossa análise foi utilizado os 2011 primeiros usuários da tabela users_dataset.
Ao visualizar as tabelas, podemos ver a necessidade de criar algumas CHAVES PRIMÁRIAS, com o objetivo de enfatizar os campos que não possuem dados repetidos. Vamos criar também algumas CHAVES ESTRAGEIRAS, com o objetivo de relacionar nossas tabelas.
alter table users_dataset add primary key (country,identifierHash);
alter table countries_with_top_seller_fashion add primary key (country);
alter table comparison_of_sellers_by_gender_and_country add primary key (country,sex);
alter table countries_with_top_seller_fashion add constraint FK_users foreign key (country) references users_dataset (country);
alter table countries_with_top_seller_fashion add constraint FK_country_gender foreign key (country) references comparison_of_sellers_by_gender_and_country
alter table countries_with_top_seller_fashion add primary key (country);
alter table comparison_of_sellers_by_gender_and_country add primary key (country,sex);
alter table countries_with_top_seller_fashion add constraint FK_users foreign key (country) references users_dataset (country);
alter table countries_with_top_seller_fashion add constraint FK_country_gender foreign key (country) references comparison_of_sellers_by_gender_and_country
(country);
No link a seguir, temos a exportação do banco de dados E-COMMERCE, onde você encontrará todos os comandos para a criação das tabelas: https://drive.google.com/file/d/10OUQCP_ttzQMiJdLSQTLdGpAQ-_yNn2e/view
Nossas tabelas já estão prontas, agora iremos começar as análises de dados para concluir os objetivos já traçados no inicio desse portfólio.
Objetivo 1: Listar os usuários que estão localizados nos países com maiores vendas de moda.
Utilizando o seguinte comando conseguirei concluir o objetivo 1:
select A.identifierHash, A.country from users_dataset A inner join
countries_with_top_seller_fashion B on A.country = B.country
where B.country = A.country
countries_with_top_seller_fashion B on A.country = B.country
where B.country = A.country
order by A.country;
Objetivo 2: Fazer uma lista da taxa de aprovação dos produtos vendidos pelos usuários, onde uma taxa de aprovação menor que 60% será considerada ruim.
Utilizando o seguinte comando conseguirei concluir o objetivo 2:
select identifierHash,productsPassRate,productsSold,
case when productsPassRate >= 60
then 'Ótima taxa de aprovação'
else 'Péssima taxa de aprovação'
end as status_aprovacao
from users_dataset
case when productsPassRate >= 60
then 'Ótima taxa de aprovação'
else 'Péssima taxa de aprovação'
end as status_aprovacao
from users_dataset
where productsSold >= 1;
Apesar do objetivo 2 já está concluído, podemos fazer uma análise simples do número de usuários que tem uma boa taxa de aprovação, para isso iremos criar uma view :
Create view vw_taxa_aprovacao as select identifierHash,productsPassRate,productsSold,
case when productsPassRate >= 60
then 'Ótima taxa de aprovação'
else 'Péssima taxa de aprovação'
end as status_aprovacao
from users_dataset
case when productsPassRate >= 60
then 'Ótima taxa de aprovação'
else 'Péssima taxa de aprovação'
end as status_aprovacao
from users_dataset
where productsSold >= 1;
Com a view criada (vw_taxa_aprovacao) podemos usar a função count() para contar o número de usuários que tem uma boa taxa de aprovação:
select count(status_aprovacao) from vw_taxa_aprovacao where status_aprovacao = 'Ótima taxa de aprovação';
Executando a consulta acima, observamos que do total de 2011 usuários cadastrados no E-commerce, apenas 826 usuários possuem uma ótima taxa de aprovação dos produtos vendidos.
Objetivo 3: Analisar qual dos gêneros tem maior participação nos produtos vendidos.
Utilizando o seguinte comando conseguirei concluir o objetivo 3:
select sex,sum(totalproductssold) as total_produtos_vendidos
from comparison_of_sellers_by_gender_and_country
group by sex;
Executando o código temos:
Sexo feminino: 18.708
Sexo Masculino: 5.346
Podemos concluir que 77,7% dos itens são vendidos pelo gênero feminino
Objetivo 4: Analisar os usuário que ficaram muitos dias sem fazer login na E-commerce. Isso influenciará no número de produtos vendidos por eles?
Nessa análise, vamos iniciar definindo como parâmetro a média dos produtos vendidos por todos os usuários e a média do número de dias desde o último login de todos os usuários.
Os comandos abaixo nos retornará as médias que estamos procurando.
select avg(daysSinceLastLogin) from users_dataset;
select avg(productsSold) from users_dataset;
As médias encontradas são:
- Média de produtos vendidos: 5.9682
- Média de dias desde o último login: 190.0736
Agora iremos rodar o seguinte comando:
select identifierHash,daysSinceLastLogin,productsSold,
case when daysSinceLastLogin >= 190.0736 and productsSold >= 5.9682
then 'verde'
when daysSinceLastLogin >= 190.0736 and productsSold < 5.9682
then 'vermelho'
when daysSinceLastLogin < 190.0736 and productsSold > 5.9682
then 'amarelo'
Else 'roxo'
end as status_aprovacao
from users_dataset;
Onde:
- VERDE: Cliente que NÃO logou constantemente e bateu a média de vendas .
- VERMELHO: Cliente que NÃO logou constantemente e NÃO bateu a média de vendas .
- AMARELO: Cliente que fez o login de forma constante e bateu a média de vendas.
- ROXO: Cliente que fez o login de forma constante e NÃO bateu a média de vendas.
Para analisar melhor a influência dos dias sem logar no número de itens vendidos, vamos rodar mais alguns comandos, utilizando subconsultas e a função count():
select count(status_aprovacao) from (
select identifierHash,daysSinceLastLogin,productsSold,
case when daysSinceLastLogin >= 190.0736 and productsSold >= 5.9682
then 'verde'
when daysSinceLastLogin >= 190.0736 and productsSold < 5.9682
then 'vermelho'
when daysSinceLastLogin < 190.0736 and productsSold > 5.9682
then 'amarelo'
Else 'roxo'
end as status_aprovacao
from users_dataset) X where X.status_aprovacao = 'verde';
Executando o comando acima descobrimos que o número de clientes classificados como verdes é 44.
select count(status_aprovacao) from (
select identifierHash,daysSinceLastLogin,productsSold,
case when daysSinceLastLogin >= 190.0736 and productsSold >= 5.9682
then 'verde'
when daysSinceLastLogin >= 190.0736 and productsSold < 5.9682
then 'vermelho'
when daysSinceLastLogin < 190.0736 and productsSold > 5.9682
then 'amarelo'
Else 'roxo'
end as status_aprovacao
from users_dataset) X where X.status_aprovacao = 'vermelho';
Executando o comando acima descobrimos que o número de clientes classificados como vermelho é 669.
select count(status_aprovacao) from (
select identifierHash,daysSinceLastLogin,productsSold,
case when daysSinceLastLogin >= 190.0736 and productsSold >= 5.9682
then 'verde'
when daysSinceLastLogin >= 190.0736 and productsSold < 5.9682
then 'vermelho'
when daysSinceLastLogin < 190.0736 and productsSold > 5.9682
then 'amarelo'
Else 'roxo'
end as status_aprovacao
from users_dataset) X where X.status_aprovacao = 'amarelo';
Executando o comando acima descobrimos que o número de clientes classificados como amarelo é 414.
select count(status_aprovacao) from (
select identifierHash,daysSinceLastLogin,productsSold,
case when daysSinceLastLogin >= 190.0736 and productsSold >= 5.9682
then 'verde'
when daysSinceLastLogin >= 190.0736 and productsSold < 5.9682
then 'vermelho'
when daysSinceLastLogin < 190.0736 and productsSold > 5.9682
then 'amarelo'
Else 'roxo'
end as status_aprovacao
from users_dataset) X where X.status_aprovacao = 'roxo';
Executando o comando acima descobrimos que o número de clientes classificados como roxo é 884.
Queremos saber agora a porcentagem de clientes em cada classificação:
- VERDE:
select 100*44/count(*) from users_dataset;
Executando o comando acima podemos concluir que 2.8% dos usuários batem a média de vendas quando não fazem login constantemente na E-commerce.
- VERMELHO:
select 100*669/count(*) from users_dataset;
Executando o comando acima podemos concluir que 33.2% dos usuários não batem a média de vendas quando não fazem login constantemente na E-commerce.
- AMARELO:
select 100*414/count(*) from users_dataset;
Executando o comando acima podemos concluir que 20.5% dos usuários batem a média de vendas quando fazem login constantemente na E-commerce.
- ROXO:
select 100*884/count(*) from users_dataset;
Executando o comando acima podemos concluir que 43.9% dos usuários não batem a média de vendas quando fazem login constantemente na E-commerce.
Podemos concluir que quando o usuário não faz o login constantemente, a média de vendas é sim influenciada, sendo que 33.2% dos usuários caem nessa categoria. Em contrapartida, vemos que boa parte dos usuários (43.9%) fazem o login constantemente e mesmo assim não batem a média de vendas.
Contudo, se conseguíssemos elevar o número de usuários que logam constantemente, isso poderia influenciar no aumento de itens vendidos, visto que um percentual considerável (20.5%) dos usuários batem a média de vendas quando logam constantemente.
